<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 4.2.0">
<meta name="google-site-verification" content="SByO7HWUKwufO-witstBrFt-yyK6q3tUlCyYwkvYPiQ" />
<meta name="baidu-site-verification" content="orhaMjPwcS" />
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">

<link rel="stylesheet" href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic|Roboto Slab:300,300italic,400,400italic,700,700italic|Roboto Mono:300,300italic,400,400italic,700,700italic&display=swap&subset=latin,latin-ext">
<link rel="stylesheet" href="/lib/font-awesome/css/font-awesome.min.css">
  <link rel="stylesheet" href="//cdn.jsdelivr.net/gh/fancyapps/fancybox@3/dist/jquery.fancybox.min.css">

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"lixiyan4633.gitee.io","root":"/","scheme":"Pisces","version":"7.7.2","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":true,"show_result":true,"style":"mac"},"back2top":{"enable":true,"sidebar":false,"scrollpercent":false},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":true,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":false,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},"path":"search.xml"};
  </script>

  <meta name="description" content="Apache Calcite 是一款开源SQL解析工具，我们将通过一系列的教程来了解calcite的知识">
<meta property="og:type" content="article">
<meta property="og:title" content="calcite教程01-calcite的基本概念">
<meta property="og:url" content="https://lixiyan4633.gitee.io/2020/03/18/calcite%E6%95%99%E7%A8%8B01-calcite%E7%9A%84%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5/index.html">
<meta property="og:site_name" content="喜延的个人博客">
<meta property="og:description" content="Apache Calcite 是一款开源SQL解析工具，我们将通过一系列的教程来了解calcite的知识">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://tva1.sinaimg.cn/large/00831rSTly1gcyeie5z1zj30il0fqgom.jpg">
<meta property="og:image" content="https://tva1.sinaimg.cn/large/00831rSTly1gcyeiiozxej30xc02qjsa.jpg">
<meta property="og:image" content="https://tva1.sinaimg.cn/large/00831rSTly1gcyeioqmloj30ps07pad5.jpg">
<meta property="og:image" content="https://tva1.sinaimg.cn/large/00831rSTly1gcyeish43ej30s00ccjtj.jpg">
<meta property="article:published_time" content="2020-03-18T09:48:06.000Z">
<meta property="article:modified_time" content="2020-03-18T13:28:22.630Z">
<meta property="article:author" content="李喜延">
<meta property="article:tag" content="calcite">
<meta property="article:tag" content="SQL">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://tva1.sinaimg.cn/large/00831rSTly1gcyeie5z1zj30il0fqgom.jpg">

<link rel="canonical" href="https://lixiyan4633.gitee.io/2020/03/18/calcite%E6%95%99%E7%A8%8B01-calcite%E7%9A%84%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true
  };
</script>

  <title>calcite教程01-calcite的基本概念 | 喜延的个人博客</title>
  






  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <div>
      <a href="/" class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">喜延的个人博客</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
        <p class="site-subtitle">我的大数据的之路</p>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
    </div>
  </div>
</div>


<nav class="site-nav">
  
  <ul id="menu" class="menu">
        <li class="menu-item menu-item-home">

    <a href="/" rel="section"><i class="fa fa-fw fa-home"></i>首页</a>

  </li>
        <li class="menu-item menu-item-tags">

    <a href="/tags/" rel="section"><i class="fa fa-fw fa-tags"></i>标签</a>

  </li>
        <li class="menu-item menu-item-categories">

    <a href="/categories/" rel="section"><i class="fa fa-fw fa-th"></i>分类</a>

  </li>
        <li class="menu-item menu-item-archives">

    <a href="/archives/" rel="section"><i class="fa fa-fw fa-archive"></i>归档</a>

  </li>
        <li class="menu-item menu-item-about">

    <a href="/about/" rel="section"><i class="fa fa-fw fa-user"></i>关于</a>

  </li>
  </ul>

</nav>
</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content">
            

  <div class="posts-expand">
      
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block " lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://lixiyan4633.gitee.io/2020/03/18/calcite%E6%95%99%E7%A8%8B01-calcite%E7%9A%84%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="https://tva1.sinaimg.cn/large/00831rSTly1gcyel996b4j30oa0oc1kx.jpg">
      <meta itemprop="name" content="李喜延">
      <meta itemprop="description" content="愿世界和平">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="喜延的个人博客">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          calcite教程01-calcite的基本概念
        </h1>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>
              

              <time title="创建时间：2020-03-18 17:48:06 / 修改时间：21:28:22" itemprop="dateCreated datePublished" datetime="2020-03-18T17:48:06+08:00">2020-03-18</time>
            </span>
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/calcite/" itemprop="url" rel="index"><span itemprop="name">calcite</span></a>
                </span>
            </span>

          
            <div class="post-description">Apache Calcite 是一款开源SQL解析工具，我们将通过一系列的教程来了解calcite的知识<br> <img src="https://tva1.sinaimg.cn/large/00831rSTly1gcyeha1945j30re06imxs.jpg"/></div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <h2 id="1-背景"><a href="#1-背景" class="headerlink" title="1.背景"></a>1.背景</h2><p>Apache Calcite是一个面向Hadoop新的查询引擎，它提供了标准的SQL语言，多种查询和优化。</p>
<p>calcite之前叫做optiq，是作为Hive的SQL引擎，为hive提供成本规模的优化功能即CBO,（Cost Based Optimizatio）,2014年5月作为独立孵化项目开源。</p>
<p>关于Calcite 的架构有三个特点：flexible, embeddable, and extensible，就是灵活性、组件可插拔、可扩展，它的 SQL Parser 层、Optimizer 层等都可以单独使用，这也是 Calcite 受总多开源框架欢迎的原因之一。</p>
<h2 id="2-Calcite的架构"><a href="#2-Calcite的架构" class="headerlink" title="2.Calcite的架构"></a>2.Calcite的架构</h2><p>关于calcite的架构，相对于传统数据库，他将数据存储和数据处理算法和元信息存储忽略了，这样使得calcite更加兼容，适合统一查询服务。</p>
<p><img src="https://tva1.sinaimg.cn/large/00831rSTly1gcyeie5z1zj30il0fqgom.jpg" alt="image.png"></p>
<p>由上图可以看到，calcite最核心的就是optimizer优化器，一个optimizer engine包含三个组成部分：</p>
<blockquote>
<p>rules:也就是匹配规则，calcite目前内置上百种rule来优化relations expression,也支持自定义的rules</p>
<p>Metadata providers:主要向优化器提供信息,有助于指导优化器向着目标（减少整体cost）进行优化，信息可以包括行数，table哪一列是唯一列，也包括计算relNode树，执行subexpression cost的函数。</p>
<p>Planner engines:触发rules来达到指定目标，比如cost-base expression optimizer(CBO)的目标是减少cost（减少数据行数，CPU cost,IO cost等）。</p>
</blockquote>
<p>对SQL执行完整流程，分为4部分</p>
<p><img src="https://tva1.sinaimg.cn/large/00831rSTly1gcyeiiozxej30xc02qjsa.jpg" alt="image.png"></p>
<ul>
<li>Parser: 解析，calcite通过javaCC将SQL解析成未经校验的AST(Abstract Syntax Tree,即抽象语法树)。</li>
<li>Validate: 验证，主要负责校验上一步parser是否合法，如验证SQL的schema信息，字段，函数等是否存在，SQL是否合法，此步骤完成后生成RealNode。</li>
<li>Optimize：优化，也是整个核心所在，将逻辑计划RelNode转化为物理计划，主要涉及SQL优化：基于规则优化（RBO），基于代价优化（CBO）,optimizer按道理是可以选的。此步骤得到物理计划</li>
<li>Execute：执行阶段，将物理计划转成程序，变成自己平台的可执行代码。</li>
</ul>
<h2 id="3-Calcite的相关概念"><a href="#3-Calcite的相关概念" class="headerlink" title="3.Calcite的相关概念"></a>3.Calcite的相关概念</h2><h3 id="3-1-catalog"><a href="#3-1-catalog" class="headerlink" title="3.1 catalog"></a>3.1 catalog</h3><p>主要定义被SQL访问的命名空间，主要包括以下两点：</p>
<ul>
<li>schema: 主要定义schema与表的结合，有点像database，但是sechma不一定是强制的，比如同名表T1就需要加上A.T1,B.T1.</li>
<li>table：对应数据库的表</li>
<li>RelDataType：代表表的数据定义，对应表的数据列名和类型。</li>
</ul>
<p><strong>Schema</strong></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">interface</span> <span class="title">Schema</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">  <span class="function">Table <span class="title">getTable</span><span class="params">(String name)</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="function">Set&lt;String&gt; <span class="title">getTableNames</span><span class="params">()</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="function">RelProtoDataType <span class="title">getType</span><span class="params">(String name)</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="function">Set&lt;String&gt; <span class="title">getTypeNames</span><span class="params">()</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="function">Collection&lt;Function&gt; <span class="title">getFunctions</span><span class="params">(String name)</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="function">Set&lt;String&gt; <span class="title">getFunctionNames</span><span class="params">()</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="function">Schema <span class="title">getSubSchema</span><span class="params">(String name)</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="function">Set&lt;String&gt; <span class="title">getSubSchemaNames</span><span class="params">()</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="function">Expression <span class="title">getExpression</span><span class="params">(SchemaPlus parentSchema, String name)</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="function"><span class="keyword">boolean</span> <span class="title">isMutable</span><span class="params">()</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="function">Schema <span class="title">snapshot</span><span class="params">(SchemaVersion version)</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="comment">/** Table type. */</span></span><br><span class="line">  <span class="keyword">enum</span> TableType &#123;&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>



<p><strong>Table</strong></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">interface</span> <span class="title">Table</span> </span>&#123;</span><br><span class="line">  <span class="function">RelDataType <span class="title">getRowType</span><span class="params">(RelDataTypeFactory typeFactory)</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="function">Statistic <span class="title">getStatistic</span><span class="params">()</span></span>;</span><br><span class="line"></span><br><span class="line">  Schema.<span class="function">TableType <span class="title">getJdbcTableType</span><span class="params">()</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="function"><span class="keyword">boolean</span> <span class="title">isRolledUp</span><span class="params">(String column)</span></span>;</span><br><span class="line"></span><br><span class="line">  <span class="function"><span class="keyword">boolean</span> <span class="title">rolledUpColumnValidInsideAgg</span><span class="params">(String column, SqlCall call,</span></span></span><br><span class="line"><span class="function"><span class="params">      SqlNode parent, CalciteConnectionConfig config)</span></span>;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<p><strong>RelDataType</strong></p>
<p>RelDataType代表数据表的行Row的数据类型，Statistic用于统计表的相关数据，特别用于CBO用于代价计算。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">selcct id, name, cast(age as bigint) from A.INFO</span><br></pre></td></tr></table></figure>

<ul>
<li><p>Data type field: id,name</p>
</li>
<li><p>data type: bigint</p>
</li>
<li><p>A为schema</p>
</li>
<li><p>INFO:table</p>
</li>
</ul>
<h3 id="3-2-SQL-Parse-SQL-gt-SQL-Node"><a href="#3-2-SQL-Parse-SQL-gt-SQL-Node" class="headerlink" title="3.2 SQL Parse(SQL -&gt; SQL Node)"></a>3.2 SQL Parse(SQL -&gt; SQL Node)</h3><p>Calcite使用javaCC作为SQL解析，javaCC通过Calcite中定义的Parse.jj文件，生成一系列的java代码，生成的代码会把SQL转换成AST的数据结构（SQL Node类型）</p>
<blockquote>
<p>与 Javacc 相似的工具还有 ANTLR，JavaCC 中的 jj 文件也跟 ANTLR 中的 G4文件类似，Apache Spark 中使用这个工具做类似的事情。</p>
</blockquote>
<p><strong>javaCC的资料</strong></p>
<p><a href="https://links.jianshu.com/go?to=https%3A%2F%2Fwww.cnblogs.com%2FGavin_Liu%2Farchive%2F2009%2F03%2F07%2F1405029.html" target="_blank" rel="noopener">JavaCC 研究与应用( 8000字 心得 源程序)</a>；</p>
<p> <a href="https://links.jianshu.com/go?to=https%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fcn%2Fxml%2Fx-javacc%2Fpart1%2Findex.html" target="_blank" rel="noopener">JavaCC、解析树和 XQuery 语法，第 1 部分</a>；</p>
<p> <a href="https://links.jianshu.com/go?to=https%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fcn%2Fxml%2Fx-javacc%2Fpart2%2Findex.html" target="_blank" rel="noopener">JavaCC、解析树和 XQuery 语法，第 2 部分</a>；</p>
<p> <a href="https://links.jianshu.com/go?to=https%3A%2F%2Fwww.yangguo.info%2F2014%2F12%2F13%2F%E7%BC%96%E8%AF%91%E5%8E%9F%E7%90%86-Javacc%E4%BD%BF%E7%94%A8%2F" target="_blank" rel="noopener">编译原理之Javacc使用</a>；</p>
<p> <a href="https://links.jianshu.com/go?to=http%3A%2F%2Fwww.engr.mun.ca%2F~theo%2FJavaCC-Tutorial%2Fjavacc-tutorial.pdf" target="_blank" rel="noopener">javacc tutorial</a>；</p>
<p>javaCC要实现一个SQL parser，他的功能有两点，这里都需要在 jj 文件中定义</p>
<ol>
<li>设计词法和语义，定义SQL中的具体元素。</li>
<li>实现词法分析器（Lexer）和语法分析器（Parse）,完成对SQL的解析，完成相应的转换。</li>
</ol>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//org.apache.calcite.prepare.CalcitePrepareImpl</span></span><br><span class="line"><span class="comment">//... </span></span><br><span class="line"><span class="comment">//解析sql-&gt;SqlNode</span></span><br><span class="line">SqlParser parser = createParser(query.sql,  parserConfig);</span><br><span class="line">      SqlNode sqlNode;</span><br><span class="line">      <span class="keyword">try</span> &#123;</span><br><span class="line">        sqlNode = parser.parseStmt();</span><br><span class="line">        statementType = getStatementType(sqlNode.getKind());</span><br><span class="line">      &#125; <span class="keyword">catch</span> (SqlParseException e) &#123;</span><br><span class="line">        <span class="keyword">throw</span> <span class="keyword">new</span> RuntimeException(</span><br><span class="line">            <span class="string">"parse failed: "</span> + e.getMessage(), e);</span><br><span class="line">      &#125;</span><br><span class="line"><span class="comment">//...</span></span><br></pre></td></tr></table></figure>

<p>经过上述代码sql经过parser.parseStmt解析之后，会生成一个SqlNode,Debug后的对象如图所示</p>
<p><img src="https://tva1.sinaimg.cn/large/00831rSTly1gcyeioqmloj30ps07pad5.jpg" alt="image.png"></p>
<h3 id="3-3-验证和转换-（SQL-Node-gt-RelNode）"><a href="#3-3-验证和转换-（SQL-Node-gt-RelNode）" class="headerlink" title="3.3 验证和转换 （SQL Node -&gt; RelNode）"></a>3.3 验证和转换 （SQL Node -&gt; RelNode）</h3><p>经过上面的解析，会将SQL翻译成SQL 的抽象语法树 AST,SQL Node就是抽象语法树的节点Node,而Rel代表关系表达式（Relation Expression）,所以从sql node -&gt; rel node 的过程，是一个转换，一个校验的过程。</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//org.apache.calcite.prepare. Prepare</span></span><br><span class="line"><span class="comment">//function prepareSql</span></span><br><span class="line">RelRoot root =</span><br><span class="line">        sqlToRelConverter.convertQuery(sqlQuery, needsValidation, <span class="keyword">true</span>);</span><br></pre></td></tr></table></figure>

<h4 id="3-3-1-校验Validate"><a href="#3-3-1-校验Validate" class="headerlink" title="3.3.1 校验Validate"></a>3.3.1 校验Validate</h4><p>语法检查前我们要先知道元信息，这个检查会包括表名，字段名，字段类型，函数名，进行语法检查的实现如下：</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//org.apache.calcite.sql.validate.SqlValidatorImpl</span></span><br><span class="line"> <span class="function"><span class="keyword">public</span> SqlNode <span class="title">validate</span><span class="params">(SqlNode topNode)</span> </span>&#123;</span><br><span class="line">    SqlValidatorScope scope = <span class="keyword">new</span> EmptyScope(<span class="keyword">this</span>);</span><br><span class="line">    scope = <span class="keyword">new</span> CatalogScope(scope, ImmutableList.of(<span class="string">"CATALOG"</span>));</span><br><span class="line">    <span class="keyword">final</span> SqlNode topNode2 = validateScopedExpression(topNode, scope);</span><br><span class="line">    <span class="keyword">final</span> RelDataType type = getValidatedNodeType(topNode2);</span><br><span class="line">    Util.discard(type);</span><br><span class="line">    <span class="keyword">return</span> topNode2;</span><br><span class="line">  &#125;</span><br></pre></td></tr></table></figure>

<p>在对SQL node的校验过程中，会对AST的语法进行补充，例如：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">//校验前</span><br><span class="line"><span class="keyword">select</span> province,<span class="keyword">avg</span>(<span class="keyword">cast</span>(age <span class="keyword">as</span> <span class="keyword">double</span>)) <span class="keyword">from</span> t_user <span class="keyword">group</span> <span class="keyword">by</span> province</span><br><span class="line">//校验后</span><br><span class="line"><span class="keyword">SELECT</span> <span class="string">`T_USER`</span>.<span class="string">`PROVINCE`</span>, <span class="keyword">AVG</span>(<span class="keyword">CAST</span>(<span class="string">`T_USER`</span>.<span class="string">`AGE`</span> <span class="keyword">AS</span> <span class="keyword">DOUBLE</span>))</span><br><span class="line"><span class="keyword">FROM</span> <span class="string">`TEST_PRO`</span>.<span class="string">`T_USER`</span> <span class="keyword">AS</span> <span class="string">`T_USER`</span></span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> <span class="string">`T_USER`</span>.<span class="string">`PROVINCE`</span></span><br></pre></td></tr></table></figure>

<h4 id="3-3-2-SQL-Node-gt-Rel-Node"><a href="#3-3-2-SQL-Node-gt-Rel-Node" class="headerlink" title="3.3.2 SQL Node-&gt;Rel Node"></a>3.3.2 SQL Node-&gt;Rel Node</h4><p>在经过上面进行合法的补充了一定语义的AST树之后，而validate中已经准备好各种映射信息后，就开始具体的RelNode的转换了。</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//org.apache.calcite.sql2rel.SqlToRelConverter</span></span><br><span class="line"> <span class="function"><span class="keyword">protected</span> RelRoot <span class="title">convertQueryRecursive</span><span class="params">(SqlNode query, <span class="keyword">boolean</span> top,</span></span></span><br><span class="line"><span class="function"><span class="params">      RelDataType targetRowType)</span> </span>&#123;</span><br><span class="line">    <span class="keyword">final</span> SqlKind kind = query.getKind();</span><br><span class="line">    <span class="keyword">switch</span> (kind) &#123;</span><br><span class="line">    <span class="keyword">case</span> SELECT:</span><br><span class="line">      <span class="keyword">return</span> RelRoot.of(convertSelect((SqlSelect) query, top), kind);</span><br><span class="line">    <span class="keyword">case</span> INSERT:</span><br><span class="line">      <span class="keyword">return</span> RelRoot.of(convertInsert((SqlInsert) query), kind);</span><br><span class="line">    <span class="keyword">case</span> DELETE:</span><br><span class="line">      <span class="keyword">return</span> RelRoot.of(convertDelete((SqlDelete) query), kind);</span><br><span class="line">    <span class="keyword">case</span> UPDATE:</span><br><span class="line">      <span class="keyword">return</span> RelRoot.of(convertUpdate((SqlUpdate) query), kind);</span><br><span class="line">    <span class="keyword">case</span> MERGE:</span><br><span class="line">      <span class="keyword">return</span> RelRoot.of(convertMerge((SqlMerge) query), kind);</span><br><span class="line">    <span class="keyword">case</span> UNION:</span><br><span class="line">    <span class="keyword">case</span> INTERSECT:</span><br><span class="line">    <span class="keyword">case</span> EXCEPT:</span><br><span class="line">      <span class="keyword">return</span> RelRoot.of(convertSetOp((SqlCall) query), kind);</span><br><span class="line">    <span class="keyword">case</span> WITH:</span><br><span class="line">      <span class="keyword">return</span> convertWith((SqlWith) query, top);</span><br><span class="line">    <span class="keyword">case</span> VALUES:</span><br><span class="line">      <span class="keyword">return</span> RelRoot.of(convertValues((SqlCall) query, targetRowType), kind);</span><br><span class="line">    <span class="keyword">default</span>:</span><br><span class="line">      <span class="keyword">throw</span> <span class="keyword">new</span> AssertionError(<span class="string">"not a query: "</span> + query);</span><br><span class="line">    &#125;</span><br><span class="line">  &#125;</span><br></pre></td></tr></table></figure>

<p>测试SQL</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> province,<span class="keyword">avg</span>(<span class="keyword">cast</span>(age <span class="keyword">as</span> <span class="keyword">double</span>)) <span class="keyword">from</span> t_user <span class="keyword">group</span> <span class="keyword">by</span> province</span><br></pre></td></tr></table></figure>

<p>得到的语法树</p>
<figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">Plan after converting SqlNode to RelNode</span><br><span class="line">LogicalAggregate(group=[&#123;<span class="number">0</span>&#125;], EXPR$1=[AVG($1)])</span><br><span class="line">  LogicalProject(PROVINCE=[$3], $f1=[CAST($2)<span class="symbol">:DOUBLE</span>])</span><br><span class="line">    LogicalTableScan(table=[[TEST_PRO, T_USER]])</span><br></pre></td></tr></table></figure>

<h3 id="3-4-优化-Optimizer"><a href="#3-4-优化-Optimizer" class="headerlink" title="3.4 优化 Optimizer"></a>3.4 优化 Optimizer</h3><p>在calcite中有两种优化器</p>
<ul>
<li><p>HepPlanner(RBO)：它是一个启发式的优化器，按照规则进行匹配，知道达到次数限制，或者遍历一遍，不在出现rule match为止。</p>
</li>
<li><p>VolcanoPlanner(RBO+CBO)：他会一直迭代rule,知道找到最小的cost的plan</p>
</li>
</ul>
<p>这里看下calcite 优化器的实现，RelOptPlanner是Calcite的基类，其子类实现如下图所示：</p>
<p><img src="https://tva1.sinaimg.cn/large/00831rSTly1gcyeish43ej30s00ccjtj.jpg" alt="image.png"></p>
<h4 id="3-4-1-基于规则的优化（RBO）"><a href="#3-4-1-基于规则的优化（RBO）" class="headerlink" title="3.4.1 基于规则的优化（RBO）"></a>3.4.1 基于规则的优化（RBO）</h4><p>基于规则的优化器（Rule-Based Optimizer，RBO）：根据优化规则对关系表达式进行转换，这里说的是，一个表达式A经过优化 得到另一个表达式B，得到B的同时A被裁掉，经过一系列转换得到最终的结果。</p>
<p>RBO有着一套严格顺序的优化规则，同样一条SQL，无论读取表中数据是什么样的，最后生成的执行计划都是相同的，所以SQL的性能可能和SQL本身的书写有很大的关系。</p>
<h4 id="3-4-2-基于代价的优化-（CBO）"><a href="#3-4-2-基于代价的优化-（CBO）" class="headerlink" title="3.4.2 基于代价的优化 （CBO）"></a>3.4.2 基于代价的优化 （CBO）</h4><p>基于代价的优化器(Cost-Based Optimizer，CBO)：也是根据优化规则对表达式进行转换，这里不同的是，表达式A在转换表达式B后，会被保存下来，根据不同的规则生成多个不同的表达式，CBO会根据统计模型和代价模型，在多个表达式选择一个cost最小的计划作为整个执行计划。</p>
<p>由上可知，CBO有两个依赖，一个是统计模型，一个是代价模型，目前大多数的计算引擎都趋向CBO，但是CBO比较有难度，因为不能明确预知数据量大小，所以CBO主要用于离线的场景。</p>

    </div>

    
    
    

      <footer class="post-footer">
          <div class="post-tags">
              <a href="/tags/calcite/" rel="tag"><i class="fa fa-tag"></i> calcite</a>
              <a href="/tags/SQL/" rel="tag"><i class="fa fa-tag"></i> SQL</a>
          </div>

        


        
    <div class="post-nav">
      <div class="post-nav-item">
    <a href="/2020/03/18/%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84%E7%AE%97%E6%B3%95Day02-%E5%A4%8D%E6%9D%82%E5%BA%A6%E5%88%86%E6%9E%90/" rel="prev" title="数据结构算法Day02-复杂度分析">
      <i class="fa fa-chevron-left"></i> 数据结构算法Day02-复杂度分析
    </a></div>
      <div class="post-nav-item">
    <a href="/2020/03/18/calcite%E6%95%99%E7%A8%8B02-calcite%E7%9A%84%E5%BF%AB%E9%80%9F%E5%85%A5%E9%97%A8/" rel="next" title="calcite教程02-calcite的快速入门">
      calcite教程02-calcite的快速入门 <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  
<div>
    
    <div>
    
        <div style="text-align:center;color: #ccc;font-size:14px;">-------------　　　　本文结束　<i class="fa fa-heart"></i>　感谢您的阅读　　　　-------------</div>
    
</div>


    
 </div>

  </div>


          </div>
          

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#1-背景"><span class="nav-number">1.</span> <span class="nav-text">1.背景</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#2-Calcite的架构"><span class="nav-number">2.</span> <span class="nav-text">2.Calcite的架构</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#3-Calcite的相关概念"><span class="nav-number">3.</span> <span class="nav-text">3.Calcite的相关概念</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#3-1-catalog"><span class="nav-number">3.1.</span> <span class="nav-text">3.1 catalog</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#3-2-SQL-Parse-SQL-gt-SQL-Node"><span class="nav-number">3.2.</span> <span class="nav-text">3.2 SQL Parse(SQL -&gt; SQL Node)</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#3-3-验证和转换-（SQL-Node-gt-RelNode）"><span class="nav-number">3.3.</span> <span class="nav-text">3.3 验证和转换 （SQL Node -&gt; RelNode）</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#3-3-1-校验Validate"><span class="nav-number">3.3.1.</span> <span class="nav-text">3.3.1 校验Validate</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#3-3-2-SQL-Node-gt-Rel-Node"><span class="nav-number">3.3.2.</span> <span class="nav-text">3.3.2 SQL Node-&gt;Rel Node</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#3-4-优化-Optimizer"><span class="nav-number">3.4.</span> <span class="nav-text">3.4 优化 Optimizer</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#3-4-1-基于规则的优化（RBO）"><span class="nav-number">3.4.1.</span> <span class="nav-text">3.4.1 基于规则的优化（RBO）</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#3-4-2-基于代价的优化-（CBO）"><span class="nav-number">3.4.2.</span> <span class="nav-text">3.4.2 基于代价的优化 （CBO）</span></a></li></ol></li></ol></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt="李喜延"
      src="https://tva1.sinaimg.cn/large/00831rSTly1gcyel996b4j30oa0oc1kx.jpg">
  <p class="site-author-name" itemprop="name">李喜延</p>
  <div class="site-description" itemprop="description">愿世界和平</div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/archives/">
        
          <span class="site-state-item-count">25</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
            <a href="/categories/">
          
        <span class="site-state-item-count">4</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
            <a href="/tags/">
          
        <span class="site-state-item-count">25</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>



      </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

<div class="copyright">
  
  &copy; 
  <span itemprop="copyrightYear">2020</span>
  <span class="with-love">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">李喜延</span>
</div>

        








      </div>
    </footer>
  </div>

  
  <script src="/lib/anime.min.js"></script>
  <script src="//cdn.jsdelivr.net/npm/jquery@3/dist/jquery.min.js"></script>
  <script src="//cdn.jsdelivr.net/gh/fancyapps/fancybox@3/dist/jquery.fancybox.min.js"></script>
  <script src="/lib/velocity/velocity.min.js"></script>
  <script src="/lib/velocity/velocity.ui.min.js"></script>

<script src="/js/utils.js"></script>

<script src="/js/motion.js"></script>


<script src="/js/schemes/pisces.js"></script>


<script src="/js/next-boot.js"></script>




  















  

  

</body>
</html>
